How to: Import a text file to an Access database.
Solution:
Select 'Get External Data' from the 'File' menu and select 'Import...', locate the desired text file, and click 'Import', then follow the instructions displayed by the Text Import Wizard.
NOTE: Access can import data from a delimited or a fixed-width text file. However, the data must be arranged so that the file has the same type of data in each column (field) and the same fields in every row (record).
1) If the destination database (the database to which to import a text file) is not already open, open the database.
2) If the Database window is not active, activate the Database window.
3) Select the 'File' menu and select 'Get External Data'. (A submenu appears.)
4) Select 'Import...' from the submenu. (The Import dialog box appears.)
5) Select 'Text Files (*.txt, *.csv, *.tab, *.asc)' from the 'Files of type' drop-down list.
6) Select the drive where the text file is located from the 'Look in' drop-down list.
7) Select the folder that contains the file from the 'Look in' list box. (The text file is displayed.)
NOTE: If the file is in a subfolder, continue selecting folders until the desired file is displayed.
8) Select the text file. (The file is highlighted.)
9) Click 'Import'. (The Text Import Wizard appears.)
Importing a text file
10) Do one of the following:
11) Click 'Next'.
12) Do one of the following:
a) If the 'Delimited' radio button is selected in Step 10):
1] Select the radio button representing the appropriate delimiter.
2] If the first row contains field names, select the 'First Row Contains Field Names' check box.
Specifying the delimiter
b) If the 'Fixed Width' radio button is selected in Step 10):
1] Click on the desired place to separate fields in the text file. (A line with an arrow appears.)
2] Repeat Step 12)b)1] to insert arrow lines between every two fields.
3] (Optional) Drag the lines to adjust the space between text columns.
Specifying text columns
NOTE: Each unit in the ruler in the middle of the dialog box stands for a character. Double-click on the line to remove it.
13) Click 'Next'.
14) Specify where the imported data is to be stored:
a) Select the 'In a New Table' radio button to store the data in a new table.
b) Select the 'In an Existing Table' radio button.
1] Click the down arrow. (A drop-down list appears.)
2] Select the table from the drop-down list.
NOTE: This option appends the data to an existing table. Select this option ONLY if the field names in the text file exactly match those in the existing table or if their column orders are the same.
Specifying the table to store the imported data
15) Click 'Next'.
16) If the data is stored in a new table, do the following:
a) (Optional) Customize fields in the text file:
Customizing fields
1] Select the field from the list box at the bottom of the 'Text Import Wizard' dialog box.
2] Do one or both of the following in the 'Field Options' group:
a] Change the name of the field in the 'Field Name' box.
b] Select one of the following indexing options from the 'Indexed' drop-down list:
1} Select 'No' to not index the field.
2} Select 'Yes (Duplicates OK)' to index the field and allow duplicates.
3} Select 'Yes (No duplicates)' to index the field without duplicates.
3] Select the 'Do not import field (Skip)' check box to not import the selected field.
b) Click 'Next'.
c) Do one of the following:
1] Select the 'Let Access add Primary Key' radio button to have Access set the primary key for the imported text file.
2] Select the 'Choose my own Primary Key' radio button and select the field to be used as the primary key from the drop-down list next to the radio button.
3] Select the 'No Primary Key' radio button to not set any primary key.
Setting the primary key
d) Click 'Next'.
17) Do one of the following in the 'Import to Table' box:
a) Type a name for the new table in the 'Import to Table' box.
b) Accept the name suggested by Access.
NOTE: If the 'In an Existing Table' radio button is selected in Step 14)a), the name of the existing table automatically displays in the box.
Typing a name for the table
18) Click 'Finish' to start the import process. (A message displays when the import is complete.)
19) Click 'OK' to close the Text Import Wizard.
20) Make sure the data types or other field properties assigned by Access are correct:
21) (Optional) Repeat steps 3) through 20) to import another text file.
